﻿/**
 * This stored procedure aims to insert a Test Session
 * For this Test Session to run you need to :
 *  - Create a C:\Zen4Sync\ directory
 *  - Copy Zen4SyncClient.exe and its dependencies into C:\Zen4Sync\Zen4SyncClient\bin\
 *  - Create an empty C:\Zen4Sync\Zen4SyncClient\ClientTestSessionDescriptors\ directory
 */
CREATE PROCEDURE [Admin].[sp_z4s_insert_test_data_Client_complex]
      @param_secondsDelayUntilStart INT = 45
    , @param_zen4SyncEncryptionKeyPassword NVARCHAR(200)
AS

DECLARE @testServerId UNIQUEIDENTIFIER;
DECLARE @testSessionId UNIQUEIDENTIFIER;
DECLARE @testScenarioId UNIQUEIDENTIFIER;
DECLARE @clientSessionId UNIQUEIDENTIFIER;

SET @testServerId = '16eaa41e-e9da-4a2b-8a92-767bd2cca9fa';
SET @testSessionId = '44accb76-49cc-4b14-baf0-34635fd6f5ea';
SET @testScenarioId = 'b80f5358-c44f-4a2b-a2bd-bad5f79b5a0e';
SET @clientSessionId = 'c5bc0460-75d4-4de8-af18-d87cb4a70c62';

INSERT INTO [Admin].[testSessionCategory]
           ([testSessionCategory_id]
           ,[testSessionCategory_name])
     VALUES
           ('F2ADD315-F176-481D-80AC-B0027726CE67'
           ,'Saturday Category')

INSERT INTO [Admin].[testServer]
           ([testServer_Id]
           ,[testServer_name]
           ,[testServer_hostName]
           ,[testServer_wcfConfigSectionName]
           ,[testServer_loadSyncApplicationPath]
           ,[testServer_configFilesDirectory]
           ,[testServer_mefComponentRootDirectory])
     VALUES
           (@testServerId
           ,'Test Server Albiona'
           ,'TestServerHostName_1'
           ,'TestServerAlbiona'
           ,'C:\Zen4Sync\Zen4SyncClient\bin\Zen4SyncClient.exe'
           ,'C:\Zen4Sync\Zen4SyncClient\ClientTestSessionDescriptors'
           ,null)

INSERT INTO [Admin].[testSession]
           ([testSession_id]
           ,[testSession_name]
           ,[testSession_createdOn]
           ,[testSession_testSessionCategoryId]
           ,[testSession_testSessionStateId])
     VALUES
           (@testSessionId
           ,'Saturday Test Session'
           ,GETDATE()
           ,'F2ADD315-F176-481D-80AC-B0027726CE67'
           ,'9f5e44e3-96ec-467d-a0c7-a7e94d3c8b88') -- This Test Session State id corresponds to 'Planned'

INSERT INTO [Scenario].[testScenario]
           ([testScenario_id]
           ,[testScenario_name])
     VALUES
           (@testScenarioId
           ,'Saturday Test Scenario')

INSERT INTO [Admin].[clientSession]
           ([clientSession_id]
           ,[clientSession_name]
           ,[clientSession_testServerId]
           ,[clientSession_testSessionId]
           ,[clientSession_enableLog]
           ,[clientSession_scheduledStartDateTime]
           ,[clientSession_testScenarioId]
           ,[clientSession_readyToStart])
     VALUES
           (@clientSessionId
           ,'ClientSession Test'
           ,@testServerId
           ,@testSessionId
           ,'1'
           ,DATEADD(SECOND, @param_secondsDelayUntilStart, GETDATE())
           ,@testScenarioId
           ,'0')

-- Activity 1 : SQL Server Execution
DECLARE @activityItem1Id UNIQUEIDENTIFIER;
DECLARE @itemContext1Id UNIQUEIDENTIFIER;
DECLARE @activity1Id UNIQUEIDENTIFIER;
DECLARE @context1Id UNIQUEIDENTIFIER;

SET @activityItem1Id = '228f7430-4f5a-4436-a7e9-a31671128944';
SET @itemContext1Id = 'd49b1001-b88b-4c25-b8d5-dc5539cf7d2f';
SET @activity1Id = 'aac891bb-0f67-4b55-b108-37985e4dfb8d';
SET @context1Id = '5792a878-99a9-45c2-a7e2-746b30aa89c8';

INSERT INTO [SQLServerExecution].[sqlServerExecution]
           ([sqlServerExecution_id]
           ,[sqlServerExecution_name]
           ,[sqlServerExecution_sqlQuery])
     VALUES
           (@activityItem1Id
           ,'Customer Update'
           ,'UPDATE Customer SET c_name = @Name WHERE c_id = @Id')

INSERT INTO [SQLServerExecution].[sqlServerExecutionContext]
           ([sqlServerExecutionContext_id]
		   ,[sqlServerExecutionContext_name]
           ,[sqlServerExecutionContext_connectionString]
           ,[sqlServerExecutionContext_isEncryptedConnectionString])
     VALUES
           (@itemContext1Id
		   ,'Customer Update Context'
           --, EXEC [Admin].sp_z4s_encrypt_data('Server=localhost\SQL2008R2;Database=Zen4SyncTestBusiness; Trusted_Connection=yes;', @itemContext1Id, @param_zen4SyncEncryptionKeyPassword)
           , CONVERT(VARBINARY(MAX), 'Server=localhost\SQL2008R2;Database=Zen4SyncTestBusiness; Trusted_Connection=yes;')
           ,'0')
           --,'1')


INSERT INTO [SQLServerExecution].[sqlServerExecutionParameter]
           ([sqlServerExecutionParameter_id]
           ,[sqlServerExecutionParameter_name]
           ,[sqlServerExecutionParameter_value]
           ,[sqlServerExecutionParameter_sqlValueTypeId]
           ,[sqlServerExecutionParameter_sqlServerExecutionContextId])
     VALUES
           ('847DC5B4-88CA-4DBF-9CA8-2ACB0B80CF85'
           ,'@Name'
           ,CONVERT(varbinary(MAX), 'Bob from Orchestrator')
           ,'f7ad067b-06ab-41d9-89ad-7152b32f127e' -- NVarchar
           ,@itemContext1Id)

INSERT INTO [SQLServerExecution].[sqlServerExecutionParameter]
           ([sqlServerExecutionParameter_id]
           ,[sqlServerExecutionParameter_name]
           ,[sqlServerExecutionParameter_value]
           ,[sqlServerExecutionParameter_sqlValueTypeId]
           ,[sqlServerExecutionParameter_sqlServerExecutionContextId])
     VALUES
           ('8762E4FC-F8FA-49EA-BFB0-1C0D06FB9FDE'
           ,'@Id'
           ,CONVERT(varbinary(MAX), 'EC031307-41E6-435F-A058-FC1BC867B61E')
           ,'581147b9-2e26-459b-8eb8-820c7cf67745' -- UniqueIdentifier
           ,@itemContext1Id)

INSERT INTO [Scenario].[activity]
		    ([activity_id]
           ,[activity_testScenarioId]
           ,[activity_activityTypeId]
           ,[activity_activityItemId]
           ,[activity_executionOrder]
           ,[activity_preWaitingTimeInSeconds]
           ,[activity_postWaitingTimeInSeconds])
     VALUES
           (@activity1Id
           ,@testScenarioId
           ,'3F912BC2-E647-46A1-9B1A-B76D06FED3EA' -- SqlServerExecution
           ,@activityItem1Id
           ,'1'
           ,'0'
           ,'0')

INSERT INTO [Scenario].[activityContext]
           ([activityContext_id]
           ,[activityContext_activityId]
           ,[activityContext_clientSessionId]
           ,[activityContext_contextItemId]
		   ,[activityContext_overridingData])
     VALUES
           (@context1Id
           ,@activity1Id
           ,@clientSessionId
           ,@itemContext1Id
		   ,NULL)

-- Activity 2: SQL Server Check Data
DECLARE @activityItem2Id UNIQUEIDENTIFIER;
DECLARE @itemContext2Id UNIQUEIDENTIFIER;
DECLARE @activity2Id UNIQUEIDENTIFIER;
DECLARE @context2Id UNIQUEIDENTIFIER;

SET @activityItem2Id = '102d3cbc-992b-4737-8a1f-4363cccd6192';
SET @itemContext2Id = '4a4e6bc4-601a-49c9-a637-b8e42b55338b';
SET @activity2Id = 'e029fe03-8d5c-480c-af46-c499ff8299a5';
SET @context2Id = '33717460-7b3f-4100-81aa-cf46f2e3f7bb'

INSERT INTO [SQLServerExecution].[sqlServerCheckData]
           ([sqlServerCheckData_id]
           ,[sqlServerCheckData_name]
           ,[sqlServerCheckData_sqlQuery]
           ,[sqlServerCheckData_expectedValue]
           ,[sqlServerCheckData_sqlValueTypeId])
     VALUES
           (@activityItem2Id
           ,'Customer Check Age'
           ,'SELECT c_age FROM Customer WHERE c_id = @Id'
           ,CONVERT(varbinary(MAX), '32') 
           ,'65a98742-b90b-4735-8349-7a3ab63e7f44') -- Int

INSERT INTO [SQLServerExecution].[sqlServerCheckDataContext]
           ([sqlServerCheckDataContext_id]
		   ,[sqlServerCheckDataContext_name]
           ,[sqlServerCheckDataContext_connectionString]
           ,[sqlServerCheckDataContext_isEncryptedConnectionString])
     VALUES
           (@itemContext2Id
		   ,'Customer Check Age Context'
           --, [Admin].sp_z4s_encrypt_data('Server=localhost\SQL2008R2;Database=Zen4SyncTestBusiness; Trusted_Connection=yes;', @itemContext2Id, @param_zen4SyncEncryptionKeyPassword)
           , CONVERT(VARBINARY(MAX),'Server=localhost\SQL2008R2;Database=Zen4SyncTestBusiness; Trusted_Connection=yes;')
           --,'1')
           ,'0')

INSERT INTO [SQLServerExecution].[sqlServerCheckDataParameter]
           ([sqlServerCheckDataParameter_id]
           ,[sqlServerCheckDataParameter_name]
           ,[sqlServerCheckDataParameter_value]
           ,[sqlServerCheckDataParameter_sqlValueTypeId]
           ,[sqlServerCheckDataParameter_sqlServerCheckDataContext])
     VALUES
           ('3B695E10-F917-4898-B915-09E39D95E178'
           ,'@Id'
           ,CONVERT(varbinary(MAX), 'EC031307-41E6-435F-A058-FC1BC867B61E')
           ,'581147b9-2e26-459b-8eb8-820c7cf67745' -- UniqueIdentifier
           ,@itemContext2Id)

INSERT INTO [Scenario].[activity]
           ([activity_id]
           ,[activity_testScenarioId]
           ,[activity_activityTypeId]
           ,[activity_activityItemId]
           ,[activity_executionOrder]
           ,[activity_preWaitingTimeInSeconds]
           ,[activity_postWaitingTimeInSeconds])
     VALUES
           (@activity2Id
           ,@testScenarioId
           ,'7E662C4A-F0B0-490C-808D-C9E5B7867C90' -- SqlServerCheckData
           ,@activityItem2Id
           ,'2'
           ,'0'
           ,'0')

INSERT INTO [Scenario].[activityContext]
           ([activityContext_id]
           ,[activityContext_activityId]
           ,[activityContext_clientSessionId]
           ,[activityContext_contextItemId]
		   ,[activityContext_overridingData])
     VALUES
           (@context2Id
           ,@activity2Id
           ,@clientSessionId
           ,@itemContext2Id
		   ,NULL)

---------------------------------------------------------------------------------------------
DECLARE @testServerId_2 UNIQUEIDENTIFIER;
DECLARE @testScenarioId_2 UNIQUEIDENTIFIER;
DECLARE @clientSessionId_2 UNIQUEIDENTIFIER;

SET @testServerId_2 = '20217765-3206-444C-84B7-5F3D499F2731';
SET @testScenarioId_2 = '57346496-78A9-44B1-8657-23B1EAB65096';
SET @clientSessionId_2 = 'FF3E4DE5-026A-4F23-BD62-DFFA7A8C8992';

INSERT INTO [Admin].[testServer]
           ([testServer_Id]
           ,[testServer_name]
           ,[testServer_hostName]
           ,[testServer_wcfConfigSectionName]
           ,[testServer_loadSyncApplicationPath]
           ,[testServer_configFilesDirectory]
           ,[testServer_mefComponentRootDirectory])
     VALUES
           (@testServerId_2
           ,'Test Server Pauline'
           ,'TestServerHostName_2'
           ,'TestServerPauline'
           ,'C:\Zen4Sync\Zen4SyncClient\bin\Zen4SyncClient.exe'
           ,'C:\Zen4Sync\Zen4SyncClient\ClientTestSessionDescriptors'
           ,null)

INSERT INTO [Scenario].[testScenario]
           ([testScenario_id]
           ,[testScenario_name])
     VALUES
           (@testScenarioId_2
           ,'Sunday Test Scenario')

INSERT INTO [Admin].[clientSession]
           ([clientSession_id]
           ,[clientSession_name]
           ,[clientSession_testServerId]
           ,[clientSession_testSessionId]
           ,[clientSession_enableLog]
           ,[clientSession_scheduledStartDateTime]
           ,[clientSession_testScenarioId]
           ,[clientSession_readyToStart])
     VALUES
           (@clientSessionId_2
           ,'ClientSession Test CE'
           ,@testServerId_2
           ,@testSessionId
           ,'1'
           ,DATEADD(SECOND, @param_secondsDelayUntilStart, GETDATE())
           ,@testScenarioId_2
           ,'0')

-- Activity 1 : SQL CE Execution
DECLARE @activityItemCE1Id UNIQUEIDENTIFIER;
DECLARE @itemContextCE1Id UNIQUEIDENTIFIER;
DECLARE @activityCE1Id UNIQUEIDENTIFIER;
DECLARE @contextCE1Id UNIQUEIDENTIFIER;

SET @activityItemCE1Id = 'EF66ACDB-0E29-4D93-8CC9-4A48E23B4C0D';
SET @itemContextCE1Id = '17941F60-9790-4E9E-8ED5-526D52FBA2DA';
SET @activityCE1Id = '51B47D0D-244A-4911-BB42-71270228774C';
SET @contextCE1Id = '8B949778-A7BB-4C46-9DA0-EC30D7B73112';

INSERT INTO [SQLCEExecution].[sqlceExecution]
           ([sqlceExecution_id]
           ,[sqlceExecution_name]
           ,[sqlceExecution_sqlQuery])
     VALUES
           (@activityItemCE1Id
           ,'Shippers Insert '
           ,'INSERT INTO Shippers ([Company Name]) VALUES (@Name)')

INSERT INTO [SQLCEExecution].[sqlceExecutionContext]
           ([sqlceExecutionContext_id]
		   ,[sqlceExecutionContext_name]
           ,[sqlceExecutionContext_connectionString]
           ,[sqlceExecutionContext_isEncryptedConnectionString])
     VALUES
           (@itemContextCE1Id
		   ,'Shippers Insert Context'
           ,convert(varbinary(MAX),'DataSource=C:\Users\Pauline\Documents\Zen4Sync\BDD\SQLCE\Northwind.sdf')
           ,'0')

INSERT INTO [SQLCEExecution].[sqlceExecutionParameter]
           ([sqlceExecutionParameter_id]
           ,[sqlceExecutionParameter_name]
           ,[sqlceExecutionParameter_value]
           ,[sqlceExecutionParameter_sqlValueTypeId]
           ,[sqlceExecutionParameter_sqlceExecutionContextId])
     VALUES
           ('275F2DB9-5D2F-4328-A524-FBFC65C5C47CC'
           ,'@Name'
           ,CONVERT(varbinary(MAX), 'My Company')
           ,'f7ad067b-06ab-41d9-89ad-7152b32f127e'
           ,@itemContextCE1Id)		   

INSERT INTO [Scenario].[activity]
		([activity_id]
           ,[activity_testScenarioId]
           ,[activity_activityTypeId]
           ,[activity_activityItemId]
           ,[activity_executionOrder]
           ,[activity_preWaitingTimeInSeconds]
           ,[activity_postWaitingTimeInSeconds])
     VALUES
           (@activityCE1Id
           ,@testScenarioId_2
           ,'A41A6035-AE9F-4397-84AC-1D396E6FC409'
           ,@activityItemCE1Id
           ,'1'
           ,'0'
           ,'0')

INSERT INTO [Scenario].[activityContext]
           ([activityContext_id]
           ,[activityContext_activityId]
           ,[activityContext_clientSessionId]
           ,[activityContext_contextItemId]
		   ,[activityContext_overridingData])
     VALUES
           (@contextCE1Id
           ,@activityCE1Id
           ,@clientSessionId_2
           ,@itemContextCE1Id
		   ,NULL)

-- Activity 2 : SQL CE Check Data
DECLARE @activityItemCE2Id UNIQUEIDENTIFIER;
DECLARE @itemContextCE2Id UNIQUEIDENTIFIER;
DECLARE @activityCE2Id UNIQUEIDENTIFIER;
DECLARE @contextCE2Id UNIQUEIDENTIFIER;

SET @activityItemCE2Id = '638AE531-C390-4A22-8422-279BE16043E7';
SET @itemContextCE2Id = 'FF148F2E-DC9D-44C4-BAFE-AB5AF1ADE914';
SET @activityCE2Id = '798E5ECB-C1E8-4F18-9E3C-9F0D5F88CACD';
SET @contextCE2Id = '57A75C06-48FD-45B0-A88A-00FD29988312';

INSERT INTO [SQLCEExecution].[sqlceCheckData]
           ([sqlceCheckData_id]
           ,[sqlceCheckData_name]
           ,[sqlceCheckData_sqlQuery]
           ,[sqlceCheckData_expectedValue]
           ,[sqlceCheckData_sqlValueTypeId])
     VALUES
           (@activityItemCE2Id
           ,'Shippers Check Company Name'
           ,'SELECT [Company Name] FROM Shippers Where [Shipper ID] = 1'
           ,CONVERT(varbinary(MAX), 'Toto')--,CONVERT(varbinary(MAX), 'Speedy Express')
           ,'f7ad067b-06ab-41d9-89ad-7152b32f127e')

INSERT INTO [SQLCEExecution].[sqlceCheckDataContext]
           ([sqlceCheckDataContext_id]
		   ,[sqlceCheckDataContext_name]
           ,[sqlceCheckDataContext_connectionString]
           ,[sqlceCheckDataContext_isEncryptedConnectionString])
     VALUES
           (@itemContextCE2Id
           ,'Shippers Check Company Name Context'
		   ,convert(varbinary(MAX),'DataSource=C:\Users\Pauline\Documents\Zen4Sync\BDD\SQLCE\Northwind.sdf')
           ,'0')

INSERT INTO [Scenario].[activity]
		([activity_id]
           ,[activity_testScenarioId]
           ,[activity_activityTypeId]
           ,[activity_activityItemId]
           ,[activity_executionOrder]
           ,[activity_preWaitingTimeInSeconds]
           ,[activity_postWaitingTimeInSeconds])
     VALUES
           (@activityCE2Id
           ,@testScenarioId_2
           ,'9DB2B54E-7CD2-4F3E-B15B-1434EAB2BE50'
           ,@activityItemCE2Id
           ,'2'
           ,'0'
           ,'0')

INSERT INTO [Scenario].[activityContext]
           ([activityContext_id]
           ,[activityContext_activityId]
           ,[activityContext_clientSessionId]
           ,[activityContext_contextItemId]
		   ,[activityContext_overridingData])
     VALUES
           (@contextCE2Id
           ,@activityCE2Id
           ,@clientSessionId_2
           ,@itemContextCE2Id
		   ,NULL)

-- Activity 3 : SQL CE Backup
--DECLARE @activityItemBackupId UNIQUEIDENTIFIER;
--DECLARE @itemContextBackupId UNIQUEIDENTIFIER;
--DECLARE @activityBackupId UNIQUEIDENTIFIER;
--DECLARE @contextBackupId UNIQUEIDENTIFIER;

--SET @activityItemBackupId = '468D037C-1F35-4198-A8E0-2E93CEF98637';
--SET @itemContextBackupId = 'E9F90A06-3E98-405D-8DBD-D575D9229C66';
--SET @activityBackupId = 'ECC27BCA-B6A3-469D-BED6-DC52F11BBF2F';
--SET @contextBackupId = '88FF263C-2052-44DA-905E-A2EFF36D0D65';

--INSERT INTO [SQLCEBackup].[sqlceBackup]
--		([sqlceBackup_id])
--	VALUES
--		(@activityItemBackupId)

--INSERT INTO [SQLCEBackup].[sqlceBackupContext]
--           ([sqlceBackupContext_id]
--		   ,[sqlceBackupContext_name]
--           ,[sqlceBackupContext_sdfFileName]
--           ,[sqlceBackupContext_sdfFileDirectory])
--     VALUES
--           (@itemContextBackupId
--           ,'NorthWind CE Backup Context'
--		   ,'Northwind.sdf'
--           ,'C:\Users\Pauline\Documents\Zen4Sync\BDD\SQLCE\')


--INSERT INTO [Scenario].[activity]
--		([activity_id]
--           ,[activity_testScenarioId]
--           ,[activity_activityTypeId]
--           ,[activity_activityItemId]
--           ,[activity_executionOrder]
--           ,[activity_preWaitingTimeInSeconds]
--           ,[activity_postWaitingTimeInSeconds])
--     VALUES
--           (@activityBackupId
--           ,@testScenarioId_2
--           ,'5B03C91F-81B0-4360-A483-5666F3B31E16'
--           ,@activityItemBackupId
--           ,'3'
--           ,'0'
--           ,'0')

--INSERT INTO [Scenario].[activityContext]
--           ([activityContext_id]
--           ,[activityContext_activityId]
--           ,[activityContext_clientSessionId]
--           ,[activityContext_contextItemId]
--		   ,[activityContext_overridingData])
--     VALUES
--           (@contextBackupId
--           ,@activityBackupId
--           ,@clientSessionId_2
--           ,@itemContextBackupId
--		   ,NULL)